package group.program.Service.impl;

import com.google.gson.Gson;
import group.program.Config.PromptConfig;
import group.program.Service.*;
import group.program.context.BaseContext;
import group.program.mapper.DatabaseMapper;
import group.program.pojo.Answer;
import group.program.pojo.Document;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

@Service
public class TextAnalysisServiceImpl implements TextAnalysisService {
    @Autowired
    private DatabaseMapper databaseMapper;

    private Logger logger = LoggerFactory.getLogger(getClass());
    // 存储上一次SQL执行的错误信息
    private String lastSqlErrorMessage = null;

    // 定义触发优先数据库查询的关键词集合
    private static final Set<String> PRIORITY_DB_KEYWORDS = new HashSet<>(Arrays.asList("图", "表格"));

    @Autowired
    private PromptConfig beginPrompt;

    @Autowired
    private NlpService nlpService;

    @Autowired
    private SqlService sqlService;

    @Autowired
    private DocumentService documentService;

    @Autowired
    private ReportService reportService;

    @Override
    public Answer textAnalysis(HttpServletResponse response, String question) {
        Answer answer = new Answer();

        try {
            // 检查是否包含关键词“图”
            boolean containsGraph = question.contains("图") || question.contains("表");

            if (containsGraph) {
                // 如果包含“图”，优先进行数据库查询
                logger.info("Question contains keyword '图'or'表'. Prioritizing database query.");
                // 首次尝试生成并执行 SQL
                List<LinkedHashMap<String, Object>> dbData = generateAndExecuteSql(response, question, false);

                if (dbData != null && !dbData.isEmpty()) {
                    // 数据库查询有结果，返回JSON格式的数据
                    Gson gson = new Gson();
                    String jsonString = gson.toJson(dbData);
                    logger.info("Database query result: " + jsonString);
                    answer.setAnswer(jsonString);
                    return answer;
                } else {
                    // 数据库查询无结果，尝试重试一次
                    logger.info(
                            "Initial database query returned no data. Attempting to regenerate SQL with clarification.");
                    dbData = generateAndExecuteSql(response, question, true);

                    if (dbData != null && !dbData.isEmpty()) {
                        // 第二次数据库查询有结果，返回JSON格式的数据
                        Gson gson = new Gson();
                        String jsonString = gson.toJson(dbData);
                        logger.info("Database query result after retry: " + jsonString);
                        answer.setAnswer(jsonString);
                        return answer;
                    } else {
                        // 第二次数据库查询无结果，尝试知识文档查询
                        logger.info(
                                "Database query after retry returned no data. Attempting knowledge document search.");
                        Answer docAnswer = handleKnowledgeDocQuery(response, question);

                        // 检查知识文档查询的回答是否为“无法生成SQL”
                        if (docAnswer != null && docAnswer.getAnswer() != null && !docAnswer.getAnswer().isEmpty()
                                && !docAnswer.getAnswer().contains("无法生成SQL")) {
                            return docAnswer;
                        } else {
                            // 知识文档查询无结果或返回“无法生成SQL”，返回抱歉信息
                            answer.setAnswer("抱歉，数据库和知识库中均未找到相关数据。");
                            return answer;
                        }
                    }
                }
            } else {
                // 如果不包含“图”，按照原有逻辑检查其他优先关键词
                boolean hasPriorityKeywords = containsKeywords(question, PRIORITY_DB_KEYWORDS);
                if (hasPriorityKeywords) {
                    // 优先查询数据库
                    logger.info("Question contains priority keywords for database query.");
                    // 首次尝试生成并执行 SQL
                    List<LinkedHashMap<String, Object>> dbData = generateAndExecuteSql(response, question, false);

                    if (dbData != null && !dbData.isEmpty()) {
                        // 数据库查询有结果，返回JSON格式的数据
                        Gson gson = new Gson();
                        String jsonString = gson.toJson(dbData);
                        logger.info("Database query result: " + jsonString);
                        answer.setAnswer(jsonString);
                        return answer;
                    } else {
                        // 数据库查询无结果，尝试重试一次
                        logger.info(
                                "Initial database query returned no data. Attempting to regenerate SQL with clarification.");
                        dbData = generateAndExecuteSql(response, question, true);

                        if (dbData != null && !dbData.isEmpty()) {
                            // 第二次数据库查询有结果，返回JSON格式的数据
                            Gson gson = new Gson();
                            String jsonString = gson.toJson(dbData);
                            logger.info("Database query result after retry: " + jsonString);
                            answer.setAnswer(jsonString);
                            return answer;
                        } else {
                            // 第二次数据库查询无结果，尝试知识文档查询
                            logger.info(
                                    "Database query after retry returned no data. Attempting knowledge document search.");
                            Answer docAnswer = handleKnowledgeDocQuery(response, question);

                            // 检查知识文档查询的回答是否为“无法生成SQL”
                            if (docAnswer != null && docAnswer.getAnswer() != null && !docAnswer.getAnswer().isEmpty()
                                    && !docAnswer.getAnswer().contains("无法生成SQL")) {
                                return docAnswer;
                            } else {
                                // 知识文档查询无结果或返回“无法生成SQL”，返回抱歉信息
                                answer.setAnswer("抱歉，数据库和知识库中均未找到相关数据。");
                                return answer;
                            }
                        }
                    }
                } else {
                    // 不包含任何优先关键词，优先进行知识文档查询
                    logger.info("Question does not contain priority keywords. Prioritizing knowledge document search.");
                    Answer docAnswer = handleKnowledgeDocQuery(response, question);

                    // 检查知识文档查询的回答是否为“无法生成SQL”
                    if (docAnswer != null && docAnswer.getAnswer() != null && !docAnswer.getAnswer().isEmpty()
                            && !docAnswer.getAnswer().contains("无法生成SQL")) {
                        return docAnswer;
                    } else {
                        // 知识文档查询无结果或返回“无法生成SQL”，尝试数据库查询
                        logger.info(
                                "Knowledge document search returned no data or cannot generate SQL. Attempting database query.");
                        // 首次尝试生成并执行 SQL
                        List<LinkedHashMap<String, Object>> dbData = generateAndExecuteSql(response, question, false);

                        if (dbData != null && !dbData.isEmpty()) {
                            // 数据库查询有结果，返回JSON格式的数据
                            Gson gson = new Gson();
                            String jsonString = gson.toJson(dbData);
                            logger.info("Database query result: " + jsonString);
                            answer.setAnswer(jsonString);
                            return answer;
                        } else {
                            // 数据库查询无结果，尝试重试一次
                            logger.info(
                                    "Initial database query returned no data. Attempting to regenerate SQL with clarification.");
                            dbData = generateAndExecuteSql(response, question, true);

                            if (dbData != null && !dbData.isEmpty()) {
                                // 第二次数据库查询有结果，返回JSON格式的数据
                                Gson gson = new Gson();
                                String jsonString = gson.toJson(dbData);
                                logger.info("Database query result after retry: " + jsonString);
                                answer.setAnswer(jsonString);
                                return answer;
                            } else {
                                // 第二次数据库查询无结果，返回抱歉信息
                                answer.setAnswer("抱歉，数据库和知识库中均未找到相关数据。");
                                return answer;
                            }
                        }
                    }
                }
            }
        } catch (Exception e) {
            logger.error("Error during processing: ", e);
            // 发生异常时，返回抱歉信息
            answer.setAnswer("抱歉，系统出现错误，无法完成您的请求。");
            return answer;
        }
    }

    /**
     * 检查用户问题中是否包含指定的关键词
     *
     * @param question 用户问题
     * @param keywords 关键词集合
     * @return 是否包含任意关键词
     */
    private boolean containsKeywords(String question, Set<String> keywords) {
        for (String keyword : keywords) {
            if (question.contains(keyword)) {
                logger.debug("Detected keyword: " + keyword);
                return true;
            }
        }
        return false;
    }

    /**
     * 生成并执行SQL查询
     *
     * @param response    HttpServletResponse 对象
     * @param question    用户问题
     * @param retryPrompt 是否为重试提示
     * @return 查询结果数据
     * @throws Exception 异常
     */
    private List<LinkedHashMap<String, Object>> generateAndExecuteSql(HttpServletResponse response, String question,
                                                                      boolean retryPrompt) throws Exception {
        String prompt;

        if (retryPrompt) {
            // 定义重试时的提示信息，包含上一次SQL执行的错误信息和具体示例
            prompt = "### 用户输入：" + question + "\n" +
                    "\n" +
                    "### 注意事项：\n" +
                    "1. 上一次生成的SQL执行时发生错误，可能是由于对用户意图的误解。\n" +
                    "2. 错误信息如下：\n" +
                    "```" + lastSqlErrorMessage + "\n" +
                    "   ```\n" +
                    "3. 请根据用户的需求重新生成正确的SQL语句。\n" +
                    "4. 如果问题不适合数据库查询，请回答“无法生成SQL”。\n" +
                    "5. 生成的SQL语句必须包裹在 ```sql 和 ``` 之间，且不应使用反引号包裹字段名称。\n" +
                    "6. 如果用户查询目的不明显，极有可能是用户想要多轮询问，如：我不想要柱状图了，我想要折线图，请根据上文回答。\n" +
                    "\n" +
                    "### 示例参考：\n" +
                    "#### 示例1：查询所有以‘溪’结尾的河流名称和长度\n" +
                    "用户输入：查询所有以‘溪’结尾的河流名称和长度\n" +
                    "生成的SQL语句：\n" +
                    "```sql\n" +
                    "SELECT name, length \n" +
                    "FROM environment.river \n" +
                    "WHERE province = '浙江省' AND name LIKE '%溪' \n" +
                    "ORDER BY length DESC;\n" +
                    "```\n" +
                    "\n" +
                    "#### 示例2：绘制2019年至2020年浙江省单位面积碳排放的折线图\n" +
                    "用户输入：绘制2019年至2020年浙江省单位面积碳排放的折线图\n" +
                    "生成的SQL语句：\n" +
                    "```sql\n" +
                    "SELECT date, carbon_emission \n" +
                    "FROM traffic.green_logistics \n" +
                    "WHERE province = '浙江省' AND date BETWEEN '2019-01-01' AND '2020-12-31' \n" +
                    "ORDER BY date ASC;\n" +
                    "```\n" +
                    "\n" +
                    "#### 示例3：查询名称中包含‘桥’的桥梁信息\n" +
                    "用户输入：查询名称中包含‘桥’的相关信息\n" +
                    "生成的SQL语句：\n" +
                    "```sql\n" +
                    "SELECT * \n" +
                    "FROM traffic.expressway_bridges \n" +
                    "WHERE name LIKE '%桥';\n" +
                    "```\n" +
                    "\n" +
                    "### 指令：\n" +
                    "请根据以上示例、用户输入和错误信息，重新理解用户的查询意图，尽量推理出用户的查询目标，生成适合当前用户输入的SQL语句。\n" +
                    "如果用户的问题描述不够清晰，请基于表信息模糊检索，尽可能准确地推理用户意图，编写查询，而不是直接放弃查询。";

        } else {
            // 首次提示
            Integer uid = BaseContext.getCurrentId();
            List<String> newPrompt = databaseMapper.selectDbStructure(uid);
            prompt = newPrompt.toString() + this.beginPrompt + "\n\n用户输入：" + question
                    + "\n请根据用户需求生成SQL语句。如果问题不适合数据库查询，首先查看上文中用户提问的信息，用户可能是想继续进行提问，若还是无法回答，请回答“无法生成SQL”。";
        }

        String sqlResponse = nlpService.getNlpApi(response, prompt).trim();
        logger.debug("LLM response for SQL generation: " + sqlResponse);

        if ("无法生成SQL".equalsIgnoreCase(sqlResponse)) {
            logger.info("LLM indicated that SQL cannot be generated for the given question.");
            return null;
        }

        // 定义正则表达式提取 SQL
        String regex = "```sql\\n([\\s\\S]*?)\\n```";
        Pattern pattern = Pattern.compile(regex);
        Matcher matcher = pattern.matcher(sqlResponse);

        String extractedSql;
        if (matcher.find()) {
            extractedSql = matcher.group(1).trim();
            logger.debug("Extracted SQL: " + extractedSql);
        } else {
            // 无法提取到 SQL，返回 null 以触发知识文档查询
            logger.warn("No SQL found in LLM response.");
            logger.debug("LLM response for SQL extraction: " + sqlResponse);
            return null;
        }

        // 执行 SQL 查询
        List<LinkedHashMap<String, Object>> queryData;
        try {
            logger.debug("Executing SQL: " + extractedSql);
            queryData = sqlService.getData(extractedSql);
            logger.debug("SQL execution successful.");
        } catch (Exception e) {
            logger.error("SQL execution error: ", e);
            // 捕获SQL执行错误信息
            lastSqlErrorMessage = e.getMessage();
            // SQL 执行出错，返回 null 以触发知识文档查询
            return null;
        }

        if (queryData == null || queryData.isEmpty()) {
            logger.info("SQL query returned no data.");
            return null; // 返回 null 以触发知识文档查询
        }

        // 如果SQL执行成功，清空错误信息
        lastSqlErrorMessage = null;

        return queryData;
    }

    /**
     * 处理知识文档查询的逻辑（基于关键词匹配）
     *
     * @param response HttpServletResponse 对象
     * @param question 用户问题
     * @return 答案对象
     * @throws Exception 异常
     */
    private Answer handleKnowledgeDocQuery(HttpServletResponse response, String question) throws Exception {
        Answer answer = new Answer();

        // 定义关键字到文档标题的映射
        Map<Set<String>, String> keywordDocumentMap = new HashMap<>();

        // 交通运输行业发展统计公报相关关键字
        Set<String> transportKeywords = new HashSet<>(Arrays.asList("桥梁"));
        keywordDocumentMap.put(transportKeywords, "2023年交通运输行业发展统计公报.pdf");

        // 大气环境状况公报相关关键字
        Set<String> airQualityKeywords = new HashSet<>(Arrays.asList("气", "AQI", "PM2.5", "污染物", "臭氧", "细颗粒物", "二级标准"));
        keywordDocumentMap.put(airQualityKeywords, "2023年浙江省大气环境状况公报.pdf");

        // 轨道交通运营里程与车辆配属数据相关关键字
        Set<String> railTransitKeywords = new HashSet<>(
                Arrays.asList("运营里程", "客运量", "投资", "地铁", "开通里程", "路", "道"));
        keywordDocumentMap.put(railTransitKeywords, "2023年浙江省轨道交通运营里程与车辆配属数据.pdf");

        // 水环境状况公报相关关键字
        Set<String> waterQualityKeywords = new HashSet<>(
                Arrays.asList("地表水", "水", "江河", "湖泊", "水库", "营养状态", "京杭运河", "断面", "河流", "河网"));
        keywordDocumentMap.put(waterQualityKeywords, "2023年浙江省水环境状况公报.pdf");

        // 遍历关键字映射表，查找匹配的文档
        Document matchedDocument = null;
        for (Map.Entry<Set<String>, String> entry : keywordDocumentMap.entrySet()) {
            Set<String> keywords = entry.getKey();
            for (String keyword : keywords) {
                if (question.contains(keyword)) {
                    matchedDocument = documentService.selectByTitle(entry.getValue());
                    if (matchedDocument != null) {
                        logger.debug("Matched document: " + matchedDocument.getTitle());
                        break;
                    }
                }
            }
            if (matchedDocument != null) {
                break; // 找到匹配的文档后退出循环
            }
        }

        if (matchedDocument != null) {
            String data = matchedDocument.getContent();
            // 调用 LLM 生成回答，返回 Markdown 格式的文本
            String prompt = data
                    + "请根据上述文档，回答以下问题，注意，此处不需要给出查询语句，只需要根据上述文档来回答，可以适当推理，并且返回纯粹的markdown格式的纯文本,不需要json包裹，但是要保持mark格式比如：### 2023年浙江省空气质量未达到国家二级标准的县级及以上城市，实际中包括markdown各种以便于观看"
                    + question;
            String nlpResponse = nlpService.getNlpApi(response, prompt).trim();
            logger.debug("LLM response for knowledge document query: " + nlpResponse);

            // 设置回答
            answer.setAnswer(nlpResponse);
            answer.setFileName(matchedDocument.getTitle());
            answer.setFileContent(matchedDocument.getContent());
            return answer;
        } else {
            // 知识文档库中也无相关数据
            logger.info("No matching document found in knowledge base.");
            answer.setAnswer("无法生成SQL"); // 修改为返回特定标识，以便后续处理
            return answer;
        }
    }
}